"""
名称：mission_05_转移数据
版本：v1.0
功能：
这个用来是把Excel表的文件转移到mysql的。
时间：2020-12-18
作者：长毛怪^^
"""

from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import pandas as pd

Base = declarative_base()


class User_table(Base):
    __tablename__ = 'common_tables'
    id = Column(Integer(), primary_key=True)
    commons = Column(String(500))
    stars = Column(Integer())


dburl = "mysql+pymysql://admin:Wmdx!123456@192.168.1.168:3306/mission_04?charset=utf8mb4"
engine = create_engine(dburl, encoding="utf-8")
Base.metadata.create_all(engine)

SessionClass = sessionmaker(bind=engine)
session = SessionClass()


def add_data():
    data = pd.read_excel('./static_resources/大护法短评.xlsx')
    for i in range(len(data)):
        user_demo = User_table(id=i+1,
                               commons=data["commons"].loc[i],
                               stars=int(data['stars'].loc[i])
                               )
        session.add(user_demo)
        session.commit()


if __name__ == '__main__':
    add_data()
